<?php
class Grafik_Model extends Model{

	public function creategraph()
	{
		$this->load->library('graph');
		$data_1 = array();
		for( $i=0; $i<7; $i++ )
		{
			$data_1[] = rand(30,550);
		}
		$ff = new graph();
		$ff->set_data( $data_1 );
		$ff->title( 'Traffic SMS 1 Minggu Terakhir', '{font-size: 14px; color: #3D5570;font-family:calibri;}' );
		$ff->line_dot( 3, 5, '#8B6122', 'SMS Jarkom', 10 );
		$ff->bg_colour = '#FFFFFF';
		$ff->x_axis_colour( '#818D9D', '#ADB5C7' );
		$ff->y_axis_colour( '#818D9D', '#ADB5C7' );
		$ff->set_x_labels( array( '25/12','26/12','27/12','28/12','29/12','30/12','31/12' ) );
		$ff->set_y_max( 600 );
		$ff->y_label_steps( 10 );
		$ff->set_y_legend( 'Jumlah SMS', 12, '#3D5570' );
		$ff->set_x_legend( 'Tanggal', 12, '#3D5570' );
		$ff->set_output_type('js');
		$ff->width = '90%';
		$ff->height = '300';
		return $ff->render();
	}
	
	function getGrafikPrestasi($tahun){
		/*$queri = 
				'SELECT NM_PRESTASI AS prestasi,COUNT(NM_PRESTASI) AS jumlah
				FROM ref_prestasi, ref_siswa, rel_siswa_prestasi
				WHERE rel_siswa_prestasi.id_siswa=ref_siswa.id
				AND ref_prestasi.id=rel_siswa_prestasi.id_prestasi 
				AND EXTRACT(YEAR FROM tanggal_diterima) = '.$tahun.' 
				GROUP BY NM_PRESTASI';
		*/
		$queri = 	'SELECT NAMA_PROYEK AS nm_proyek, COUNT(NAMA_PROYEK) AS jumlah
					FROM proyek, evaluasi 
					WHERE ID_PROYEK=proyek.ID_PROYEK AND tahun='.$tahun.'
					GROUP BY NM_PRESTASI';
		$data = $this->db->query($queri);
		return $data;
	}
	
	function getTahunAjar(){
		$queri = 'SELECT EXTRACT(YEAR FROM tanggal_diterima) AS tahun FROM ref_siswa GROUP BY tahun';
		$query = $this->db->query($queri);
		$form ="";
		if ($query->num_rows() > 0)
        {
           foreach ($query->result_array() as $row)
           {
				$value = $row['tahun'];
				$data = $row['tahun'];
                $form .= '<option value="'.$value.'">'.$data."</option>\n";
           }
        }
		return $form;
	}
	
	function getTahunPrestasi(){
		$queri = "SELECT DISTINCT tahun FROM rel_siswa_prestasi";
		$query = $this->db->query($queri);
				$form ="";
		if ($query->num_rows() > 0){
           foreach ($query->result_array() as $row){
				$value = $row['tahun'];
				$data = $row['tahun'];
                $form .= '<option value="'.$value.'">'.$data."</option>\n";
           }
        }
		return $form;
	}
	
	function getPeriodeEskul(){
		$queri ='SELECT DISTINCT tahun FROM rel_eskul ';
		$query = $this->db->query($queri);
		$form ="";
		if($query->num_rows() > 0){
			foreach ($query->result_array() as $row){
				$value = $row['tahun'];
				$data = $row['tahun'];
			$form .= '<option value="'.$value.'">'.$data."</option>\n";
			}
		}
		return $form;
	}
	
	function getGrafikEskul($tahun){
		$queri = "SELECT nm_eskul as nama, COUNT(id_eskul) as jumlah FROM rel_eskul, ref_eskul
			WHERE id_eskul= ref_eskul.id AND tahun=".$tahun." GROUP BY id_eskul ";
		$data= $this->db->query($queri);
		return $data;
	}
	
	function getGrafikEkonomi($tahun){
		$queri = 	'SELECT rank as nama, COUNT(gaji) as jumlah FROM ref_siswa, ref_reng_ekonomi
					WHERE ref_siswa.gaji= ref_reng_ekonomi.id
					GROUP BY gaji';
		$data= $this->db->query($queri);
		return $data;
	}
}